package vn.edu.saigontech.ncms.web.student;

import java.io.IOException;
import java.util.List;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import vn.edu.saigontech.ncms.model.info.Student;
import vn.edu.saigontech.ncms.model.info.SurveyInfo;
import vn.edu.saigontech.ncms.service.StudentService;
import vn.edu.saigontech.ncms.service.SurveyInfoService;
import vn.edu.saigontech.ncms.util.Checker;
import vn.edu.saigontech.ncms.util.DateUtil;
import vn.edu.saigontech.ncms.util.ExcelUtil;
import vn.edu.saigontech.ncms.web.BaseAction;

@SuppressWarnings("serial")
public class SurveyInfoReportAction extends BaseAction {

	private String fromDate;
	private String toDate;
	
	public String showByStudentReport() {
		fromDate = DateUtil.toString(DateUtil.startOfMonth(DateUtil.newDate()), this.i18n("format.date"));
		toDate = DateUtil.toString(DateUtil.endOfMonth(DateUtil.newDate()), this.i18n("format.date"));
		return SUCCESS;
	}
	
	public String byStudentReport() {
		HSSFWorkbook wb = ExcelUtil.getTemplate("survey-info-by-student-report.xls");
		if (Checker.isNull(wb)) return NONE;
		
		List<Student> students = (List<Student>) StudentService.INSTANCE.findByConsultDate(
				DateUtil.fromString(this.fromDate, this.i18n("format.date")), 
				DateUtil.fromString(this.toDate, this.i18n("format.date")));
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "SurveyInfoReportByStudent-" + fromDate + "-" + toDate + ".xls\"");
		
		try {
			HSSFFont font11 	= this.makeFont(wb, "Times New Roman", 11, false);
			HSSFCellStyle font11Center 	= this.makeCellStyle(wb, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font11);
			HSSFCellStyle font11Left 	= this.makeCellStyle(wb, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, font11);
			
			/* by class sheet */
			HSSFSheet sheet = wb.getSheetAt(0);
			
			HSSFRow row = sheet.getRow(1);
			HSSFCell cell = row.getCell(0);
			cell.setCellValue(new HSSFRichTextString("Từ " + this.fromDate + " đến ngày " + this.toDate));
			
			int r = 4;
			for (int i = 0; i < students.size(); i++) {
				Student student = students.get(i);
				row = sheet.createRow(r);

				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(i + 1);
				cell.setCellStyle(font11Center);
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(student.getLastName()));
				cell.setCellStyle(font11Left);
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(student.getFirstName()));
				cell.setCellStyle(font11Left);
				
				cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(this.i18n(student.getGender().getKey())));
				cell.setCellStyle(font11Center);
				
				cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(DateUtil.toString(student.getDateOfBirth(), this.i18n("format.date"))));
				cell.setCellStyle(font11Center);
				
				cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
				StringBuilder infos = new StringBuilder();
				for (SurveyInfo each : student.getSurveys())
					infos.append(each.getDescription() + "\n");
				if (infos.length() > 0)
					infos.replace(infos.lastIndexOf("\n"), infos.length(), "");
				cell.setCellValue(new HSSFRichTextString(infos.toString()));
				cell.setCellStyle(font11Center);
				
				cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(new HSSFRichTextString((student.getLastOffering()!=null?student.getLastOffering().getName():"")));
				cell.setCellStyle(font11Center);
				
				cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(new HSSFRichTextString(DateUtil.toString(student.getConsultedDate(), this.i18n("format.date"))));
				cell.setCellStyle(font11Center);
				
				cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(new HSSFRichTextString(student.getComment()));
				cell.setCellStyle(font11Left);
				
				r++;
			}
			
			row = sheet.createRow(r);
			row.setHeightInPoints(25);
			
			/* misc */
			sheet.getPrintSetup().setLandscape(true);
			sheet.setMargin(HSSFSheet.TopMargin, (double) .50);
	        sheet.setMargin(HSSFSheet.BottomMargin, (double) .50);
	        sheet.setMargin(HSSFSheet.LeftMargin, (double) .50);
	        sheet.setMargin(HSSFSheet.RightMargin, (double) .50);
			//sheet.getPrintSetup().setScale((short)88);
			sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);

			wb.write(RESPONSE().getOutputStream());
		} catch (IOException e) {
			e.printStackTrace();
		}
		this.addLoggingDetail(BaseAction.REPORT, "logging.surveyInfo.byStudent.excel", fromDate, toDate);
		return NONE;
	}
	
	public String showReport() {
		fromDate = DateUtil.toString(DateUtil.startOfMonth(DateUtil.newDate()), this.i18n("format.date"));
		toDate = DateUtil.toString(DateUtil.endOfMonth(DateUtil.newDate()), this.i18n("format.date"));
		return SUCCESS;
	}
	
	public String report() {
		HSSFWorkbook wb = ExcelUtil.getTemplate("survey-info-report.xls");
		if (Checker.isNull(wb)) return NONE;
		
		List<Student> students = (List<Student>) StudentService.INSTANCE.findByConsultDate(
				DateUtil.fromString(this.fromDate, this.i18n("format.date")), 
				DateUtil.fromString(this.toDate, this.i18n("format.date")));
		List<SurveyInfo> infos = (List<SurveyInfo>) SurveyInfoService.INSTANCE.findAll();
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "SurveyInfoReport-" + fromDate + "-" + toDate + ".xls\"");
		
		try {
			HSSFFont font11 	= this.makeFont(wb, "Times New Roman", 11, false);
			HSSFCellStyle font11Center 	= this.makeCellStyle(wb, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font11);
			HSSFCellStyle font11Left 	= this.makeCellStyle(wb, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, font11);
			
			/* by class sheet */
			HSSFSheet sheet = wb.getSheetAt(0);
			
			HSSFRow row = sheet.getRow(1);
			HSSFCell cell = row.getCell(0);
			cell.setCellValue(new HSSFRichTextString("Từ " + this.fromDate + " đến ngày " + this.toDate));
			
			int r = 4;
			for (int i = 0; i < infos.size(); i++) {
				SurveyInfo info = infos.get(i);
				row = sheet.createRow(r);

				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(i + 1);
				cell.setCellStyle(font11Center);
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(info.getDescription()));
				cell.setCellStyle(font11Left);
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(countInfo(info.getId(), students));
				cell.setCellStyle(font11Center);
				
				r++;
			}
			
			row = sheet.createRow(r);
			row.setHeightInPoints(25);
			
			/* misc */
			sheet.getPrintSetup().setLandscape(false);
			sheet.setMargin(HSSFSheet.TopMargin, (double) .50);
	        sheet.setMargin(HSSFSheet.BottomMargin, (double) .50);
	        sheet.setMargin(HSSFSheet.LeftMargin, (double) .50);
	        sheet.setMargin(HSSFSheet.RightMargin, (double) .50);
			sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);

			wb.write(RESPONSE().getOutputStream());
		} catch (IOException e) {
			e.printStackTrace();
		}
		this.addLoggingDetail(BaseAction.REPORT, "logging.surveyInfo.excel", fromDate, toDate);
		return NONE;
	}

	private int countInfo(Long id, List<Student> students) {
		int count = 0;
		for (Student each : students) {
			Set<SurveyInfo> infos = each.getSurveys();
			for (SurveyInfo each2 : infos)
				if (each2.getId().equals(id))
					count++;
		}
		return count;
	}

	private HSSFCellStyle makeCellStyle(HSSFWorkbook workbook, int halign, int valign, HSSFFont font) {
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment((short) halign);
		style.setVerticalAlignment((short) valign);
		style.setWrapText(true);
		style.setFont(font);
		return style;
	}
	
	private HSSFFont makeFont(HSSFWorkbook workbook, String name, int size, boolean isBold) {
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short)size);
		font.setFontName(name);
		if(isBold) {
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		}
		return font;
	}

	public String getFromDate() {
		return fromDate;
	}

	public void setFromDate(String fromDate) {
		this.fromDate = fromDate;
	}

	public String getToDate() {
		return toDate;
	}

	public void setToDate(String toDate) {
		this.toDate = toDate;
	}
}